﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace SoftWareLab_Project
{
    public class SertData
    {
        string strConnString = WebConfigurationManager.ConnectionStrings["DB_EVALUATEConnectionString"].ConnectionString;
        public DataTable GetData()
        {     
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
       
        public DataTable GetDataHead(string Depart)
        {
            string sqltext = @"SELECT   TB_USER.NAME
                                       ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        WHERE TB_USER.ID_DEPARTMENT = " + Convert.ToInt32(Depart)+"";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        public DataTable GetDataNo(int NoId)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        WHERE TB_TOTALWORK.NO_ID =  " + NoId + " ";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];  
        }
        public DataTable GetDataFromName(string name)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.NAME LIKE  '" + name + "%'";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);    
            return ds.Tables[0];  
        }
        public DataTable GetDataFromUsername(string Username)
        {
            string sqltext = @"SELECT   TB_USER.USER_NAME
                                       ,TB_USER.NAME 
                                       ,TB_USER.LASTNAME	                                   
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.NAME =  '" + Username + "'  ";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable GetDataFromFac(int Fac)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.ID_FACULTY =  " + Fac + "";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable GetDataDepartment(int Department)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.ID_DEPARTMENT =  " + Department + "";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }

        public DataTable GetDataFromNameFac(string name,int FacultyID)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.NAME LIKE '" + name + "%'AND TB_USER.ID_FACULTY = "+ FacultyID +"";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable GetDataFromNameDep(string name, int departmentId)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.NAME LIKE '" + name + "%'AND TB_USER.ID_DEPARTMENT = " + departmentId + "";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable GetDataFromAll(string name, int FacultyID, int DepartmentID)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where TB_USER.NAME LIKE '" + name + "%'AND TB_USER.ID_FACULTY = " + FacultyID + " AND TB_USER.ID_DEPARTMENT = " + DepartmentID + "";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable GetDataFromFacDep(int FacultyID, int DepartmentID)
        {
            string sqltext = @"SELECT  TB_USER.NAME
	                                   ,TB_USER.LASTNAME
	                                   ,TB_USER.ID_FACULTY
	                                   ,TB_USER.ID_DEPARTMENT
                                       ,TB_TOTALWORK.NO_ID
	                                   ,TB_TOTALWORK.USER_NAME
	                                   ,TB_TOTALWORK.TERM
	                                   ,TB_TOTALWORK.YEAR
	                                   ,TB_TOTALWORK.TEACHING_P
	                                   ,TB_TOTALWORK.RESEARCH_P
	                                   ,TB_TOTALWORK.SERVICES_P
	                                   ,TB_TOTALWORK.OTHER_P	   
                                        FROM TB_TOTALWORK inner join TB_USER on TB_USER.USER_NAME = TB_TOTALWORK.USER_NAME
                                        Where  TB_USER.ID_FACULTY = " + FacultyID + " AND TB_USER.ID_DEPARTMENT = " + DepartmentID + "";
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}